package merlin.dao;

import java.sql.*;
import java.util.*;

import merlin.beans.*;

public class IWUserSettingsDAO{

	Connection con;

	public IWUserSettingsDAO(Connection connection){
		con = connection;
	}

	public int addIWUserSettings(IWUserSettingsBean bean)throws SQLException{

		Statement stmt = con.createStatement();
   		ResultSet rs = stmt.executeQuery("SELECT COALESCE(MAX(IW_USER_SETTINGS_ID),0) + 1 FROM NSD.IW_USER_SETTINGS");
		rs.next();
		int newID = rs.getInt(1);
		rs.close();
		stmt.close();

		String sql = "INSERT INTO NSD.IW_USER_SETTINGS(" +
					 "IW_USER_SETTINGS_ID,CUSTOMER_DETAILS_ID,STATE_NAME,SECTORS,RATIOS,GROUPS,CHARTTYPE,CHARTVISIBILITY,HIDDENFIELDS)VALUES(" +
						"?,?,?,?,?,?,?," + bean.isChartVisibility() + ",?)";

		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,newID);
		pstmt.setInt(2,bean.getCustomerDetailsId());
		pstmt.setString(3,bean.getStateName());
		pstmt.setString(4,bean.getSectors());
		pstmt.setString(5,bean.getRatios());
		pstmt.setString(6,bean.getGroups());
		pstmt.setString(7,bean.getChartType());
		pstmt.setString(8,bean.getHiddenFields());

		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return newID;
	}

	public List getIWUserSettings(int customerDetailsId,String orderBy)throws SQLException{
		String sql = "SELECT IW_USER_SETTINGS_ID,CUSTOMER_DETAILS_ID,STATE_NAME,SECTORS,RATIOS,GROUPS,CHARTTYPE,CHARTVISIBILITY,HIDDENFIELDS FROM NSD.IW_USER_SETTINGS WHERE CUSTOMER_DETAILS_ID = ? " ;
		if(orderBy != null){
			sql += " ORDER BY " + orderBy;
		}
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1,customerDetailsId);
		ResultSet rs = pstmt.executeQuery();

		List result = new ArrayList();

		while(rs.next()){
			IWUserSettingsBean bean = new IWUserSettingsBean();
			bean.setIwUserSettingsId(rs.getInt("IW_USER_SETTINGS_ID"));
			bean.setCustomerDetailsId(rs.getInt("CUSTOMER_DETAILS_ID"));
			bean.setStateName(rs.getString("STATE_NAME"));
			bean.setSectors(rs.getString("SECTORS"));
			bean.setRatios(rs.getString("RATIOS"));
			bean.setGroups(rs.getString("GROUPS"));
			bean.setChartType(rs.getString("CHARTTYPE"));
			bean.setChartVisibility(rs.getBoolean("CHARTVISIBILITY"));
			bean.setHiddenFields(rs.getString("HIDDENFIELDS"));

        	result.add(bean);
		}

		rs.close();
		pstmt.close();

		return result;
	}

	public int updateIWUserSettings(int iwUserSettingsId,String newStateName)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("UPDATE IW_USER_SETTINGS SET STATE_NAME = ? WHERE IW_USER_SETTINGS_ID = ?");
		pstmt.setString(1,newStateName);
		pstmt.setInt(2,iwUserSettingsId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}

	public int deleteIWUserSettings(int iwUserSettingsId)throws SQLException{
		PreparedStatement pstmt = con.prepareStatement("DELETE FROM IW_USER_SETTINGS WHERE IW_USER_SETTINGS_ID = ?");
		pstmt.setInt(1,iwUserSettingsId);
		int updateCols = pstmt.executeUpdate();
		pstmt.close();
		return updateCols;
	}
}